package br.edu.univas.si.es.correios.model;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;


public class JdbcDAO extends ConectDB implements BaseDAO {
	
	public void create(Order order){
		String sqlCreatPedidos = "insert into pedidos(modo_envio, cep_emit, cep_dest, estado_emit, estado_dest, cid_emit, cid_dest," +
								 "bairro_emit, bairro_dest, log_emit, log_dest, num_emit, num_dest, prazo, preco) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
		
		
		try{
			PreparedStatement pedidos = getConnection().prepareStatement(sqlCreatPedidos);
		
			pedidos.setString(1, order.getSendMode());
			pedidos.setInt(2, order.getSenderCep());
			pedidos.setInt(3, order.getDestinataryCep());
			pedidos.setString(4, order.getSenderState());
			pedidos.setString(5, order.getDestinataryState());
			pedidos.setString(6, order.getSenderCity());
			pedidos.setString(7, order.getDestinataryCity());
			pedidos.setString(8, order.getSenderNeighbor());
			pedidos.setString(9, order.getDestinataryNeighbor());
			pedidos.setString(10, order.getSenderStreet());
			pedidos.setString(11, order.getDestinataryStreet());
			pedidos.setInt(12, order.getSenderStreetNumber());
			pedidos.setInt(13, order.getDestinataryStreetNumber());
			pedidos.setFloat(14, order.getTotalCosts());
			pedidos.setInt(15, order.getDeliveryDays());
			pedidos.executeUpdate();
			System.out.println(pedidos);
			pedidos.close();
			
		}catch(Exception e){
			System.out.println("Erro: " + e.getMessage());
			e.printStackTrace();
		}
	}
	
	public Integer getOrderID(Order order){
		String sqlCreatPedidos = "SELECT last_value FROM pedidos_num_rastreamento_seq";
		
		
		try{
			
			PreparedStatement pedidos = getConnection().prepareStatement(sqlCreatPedidos);
			ResultSet result = pedidos.executeQuery();
			result.next();
			int id = result.getInt(1); 
			pedidos.close();
			return id;
			
		}catch(Exception e){
			System.out.println("Erro: " + e.getMessage());
			e.printStackTrace();
			return 0;
		}
	}
	
	public Order retrieveOne(int orderNumber){
		String sqlRetrieveOrder = "select * from pedidos where pedidos.num_rastreamento = '" + orderNumber + "'";
		Order order = new Order();
		Statement statement;
		try {
			statement = getStatement();			
			ResultSet result = statement.executeQuery(sqlRetrieveOrder);
			
			while(result.next()){
				
				order.setOrderNumber(result.getInt("num_rastreamento"));
				order.setSendMode(result.getString("modo_envio"));
				order.setSenderCep(result.getInt("cep_emit"));
				order.setDestinataryCep(result.getInt("cep_dest"));
				order.setSenderState(result.getString("estado_emit"));
				order.setDestinataryState(result.getString("estado_dest"));
				order.setSenderCity(result.getString("cid_emit"));
				order.setDestinataryCity(result.getString("cid_dest"));
				order.setSenderNeighbor(result.getString("bairro_emit"));
				order.setDestinataryNeighbor(result.getString("bairro_dest"));
				order.setSenderStreet(result.getString("log_emit"));
				order.setDestinataryStreet(result.getString("log_dest"));
				order.setSenderStreetNumber(result.getInt("num_emit"));
				order.setDestinataryStreetNumber(result.getInt("num_dest"));
				order.setTotalCosts(result.getInt("preco"));
				order.setDeliveryDays(result.getInt("prazo"));
			}
			
		} catch (SQLException e) {
			System.out.println("Erro: " + e.getMessage());
			e.printStackTrace();
		}
		return order;
	}
	
	public void update(Order order, String field, String newData){
		String sql = "update pedidos set " + field  + " = ? where pedidos.num_rastreamento = " + order.getOrderNumber(); 
						
		try {
			PreparedStatement statement = getConnection().prepareStatement(sql);
			statement.setString(1,newData);
			
		} catch (SQLException e) {
			System.out.println("ERRO AO TENTAR MODIFICAR O PEDIDO!!");
			e.printStackTrace();
		}
		
	}
	
	public void delete(Order order){	
		String sql = "delete from pedidos where pedidos.num_rastreamento = '" + order.getOrderNumber() + "'";
		
		try {
			PreparedStatement statement = getConnection().prepareStatement(sql);
			statement.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	public boolean checkOrderNumber(int orderNumber) {
		String sql = "select num_rastreamento from pedidos where pedidos.num_rastreamento = '" + orderNumber + "'";
		
		try {			
			Statement statement = getStatement();
			
			ResultSet resultCity = statement.executeQuery(sql);
			resultCity.next();
			orderNumber = (resultCity.getInt("num_rastreamento"));
							

		} catch (SQLException e) {
			//System.out.println("Erro: " + e.getMessage());
			System.out.println("ENVIO INEXISTENTE!!");
			//e.printStackTrace();
			return false;
		}
		return true;
	}
}
